package com.sq.partner.manager.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.log4j.Logger;
import org.springframework.stereotype.Repository;

import com.sq.partner.manager.common.Constants;
import com.sq.partner.manager.common.Result;
import com.sq.partner.manager.common.exception.AppException;
import com.sq.partner.manager.dao.IGatewayAgentInfoRelaDao;
import com.sq.partner.manager.mybatis.model.GatewayAgency;
import com.sq.partner.manager.mybatis.model.GatewayUser;
import com.sq.partner.manager.mybatis.model.TradAgentInfo;
import com.sq.partner.manager.mybatis.model.TradAgentInfoRela;
import com.sq.partner.manager.mybatis.model.TradInsuPlan;
import com.sq.partner.manager.util.DBUtil;
import com.sq.partner.manager.util.LogUtil;
import com.sq.partner.manager.util.Page;
import com.sq.partner.manager.util.SqlUtil;
import com.sq.partner.manager.util.StrUtil;
/**
 * 
 * @author zzguo
 *
 */
@Repository
public class IGatewayAgentInfoRelaDaoImpl extends BaseDaoImpl implements IGatewayAgentInfoRelaDao {
	
	private static Logger logger = LogUtil.getLog(IGatewayAgentInfoRelaDaoImpl.class);
	
	@Override
	public Page<GatewayAgency> queryInfoByCriteria(Page<GatewayAgency> page,GatewayUser user) throws Exception {
		Connection conn = null;
		PreparedStatement pstmt = null;
		PreparedStatement pstmt1 = null;
		ResultSet rs = null;
		try {
			String deptStr = user.getDepartmentStr();
			if(StrUtil.isNotEmpty(deptStr)){
				StringBuilder sql = new StringBuilder();
				//总公司处理
				if(deptStr.contains(Constants.HQ_BRANCH_CODE)){
					sql.append("SELECT T.AGENCY_CODE, T.AGENCY_NAME, T.DEPT_CODE, H.DEPT_CNAME"
							+ " FROM T_GATEWAY_AGENCY T INNER JOIN T_CORE_DEPT H ON T.DEPT_CODE = H.DEPT_CODE"
							+ " WHERE 1 = 1");
				}else{
					sql.append("SELECT T.AGENCY_CODE, T.AGENCY_NAME, T.DEPT_CODE, H.DEPT_CNAME"
							+ " FROM T_GATEWAY_AGENCY T INNER JOIN T_CORE_DEPT H ON T.DEPT_CODE = H.DEPT_CODE"
							+ " WHERE 1 = 1 AND T.DEPT_CODE IN ("+deptStr+")");
				}

			List<String> params = new ArrayList<String>();
			if (StrUtil.isNotEmpty(page.getType().getAgencyCode())) {
				String agencyCode = page.getType().getAgencyCode();
				String agencyCodeStr = "%" + agencyCode + "%";
				sql.append(" AND  T.AGENCY_CODE LIKE ? ");
				params.add(agencyCodeStr);
			}

			if (StrUtil.isNotEmpty(page.getType().getAgencyName())) {
				String agencyName = page.getType().getAgencyName();
				String agencyNameStr = "%" + agencyName + "%";
				sql.append(" AND T.AGENCY_NAME LIKE ? ");
				params.add(agencyNameStr);
			}
			sql.append(" ORDER BY T.AGENCY_CODE ");
			conn = this.getConnection();
			Long total = 0L;
			logger.debug("excute :sql-->    "+sql.toString());
			pstmt = conn.prepareStatement("select count(*) from ("+ sql.toString() + ")");

			for (int i = 0; i < params.size(); i++) {
				pstmt.setObject(i + 1, params.get(i));
			}

			rs = pstmt.executeQuery();
			if (rs.next()) {
				total = rs.getLong(1);
			}
			page.setTotal(total);

			SqlUtil sqlUtil = new SqlUtil();

			String sqlPage = sqlUtil.buildPageSqlForOracle(sql.toString(),page).toString();

			pstmt1 = conn.prepareStatement(sqlPage);
			for (int i = 0; i < params.size(); i++) {
				pstmt1.setObject(i + 1, params.get(i));
			}
			rs = pstmt1.executeQuery();
			List transList = DBUtil.transRs2List(rs);
			page.setData(transList);
			}
		} finally {
			close(pstmt);
			close(pstmt1);
			close(rs);
			close(conn);
		}
		return page;
	}

	@Override
	public boolean addAgencyInfo(GatewayAgency agency) throws Exception {
		return this.add("insertGatewayAgency", agency);
	}

	@Override
	public boolean updateAgencyInfo(GatewayAgency agency) throws Exception {
		return this.update("updateGatewayAgencyNotNull", agency);
	}

	@Override
	public GatewayAgency queryAgencyInfoByCode(GatewayAgency agency) throws Exception {
		return (GatewayAgency) this.query("queryGatewayAgencyById", agency.getAgencyCode());
	}

	@Override
	public boolean deleteAgencyInfo(GatewayAgency agency) throws Exception {
		return this.delete("delGatewayAgencyById", agency.getAgencyCode());
	}

	@Override
	public int checkIsExistFacadeUser(String agencyCode) throws Exception {
		return (Integer) this.query("queryRecordCountByCode", agencyCode);
	}

	@Override
	public boolean addAgentInfoRela(TradAgentInfoRela tradAgentInfoRela)
			throws Exception {
		return this.add("insertTradAgentInfoRela", tradAgentInfoRela);
	}

	@Override
	public boolean upadteAgentInfoRela(TradAgentInfoRela tradAgentInfoRela)
			throws Exception {
		return this.update("updateTradAgentInfoRelaNotNull", tradAgentInfoRela);
	}

	@Override
	public boolean deleteAgentInfoRela(TradAgentInfoRela tradAgentInfoRela)
			throws Exception {
		return this.delete("delTradAgentInfoRelaById", tradAgentInfoRela.getId());
	}

	@SuppressWarnings({ "rawtypes", "unchecked" })
	@Override
	public List<Map> queryAgentInfoByInsuPlanNo(String insuPlanNo) throws Exception{
		return this.queryList("queryTradAgentInfoByInsuPlanNo", insuPlanNo);
	}

	@Override
	public boolean delTradAgentInfoRelaByInsuplannoAndAgentIds(
			String insuPlanNo, String agentInfoIds) throws Exception {
		Map<String,String> map = new HashMap<String,String>();
		map.put("insuPlanNo", insuPlanNo);
		map.put("agentInfoIds", agentInfoIds);
		return this.delete("delTradAgentInfoRelaByInsuplannoAndAgentIds", map);
	}

	@Override
	public boolean addOrUpdateTradAgentInfoRela(TradAgentInfoRela  tradAgentInfoRela) throws Exception {
		Map<String,Object> map = new HashMap<String,Object>();
		map.put("insuPlanNo", tradAgentInfoRela.getInsuPlanNo());
		map.put("agentInfoId", tradAgentInfoRela.getAgentInfoId());
		TradAgentInfoRela oldAgentInfoRela = (TradAgentInfoRela) this.query("queryTradAgentInfoRelaByMap", map);
		boolean flag = false;
		if(oldAgentInfoRela!=null){
			flag= this.update("updateTradAgentInfoRelaByInsuPlanNoAndAgentInfoId", tradAgentInfoRela);
		}else{
			flag= this.add("insertTradAgentInfoRela", tradAgentInfoRela);
		}
		return flag;
	}

	@Override
	public boolean delTradAgentInfoRelaByPlanNo(String planNo) throws Exception {
		return this.delete("delTradAgentInfoRelaByPlanNo", planNo);
	}

	@Override
	public boolean copyTradAgentInfoRela(String newPlanNo, String oldPlanNo)
			throws Exception {
		boolean  flag =false;
		Connection conn = null;
		//查总条数
		PreparedStatement pstmt = null;
		
		try{
			conn = this.getConnection();
			String sql="insert into t_trad_agent_info_rela(id,agent_info_id,insu_plan_no,comm_rate,oper_user,oper_time,MARKET_RATE,BUSINESS_RATE) "
					+" select S_TRAD_AGENT_INFO_RELA.nextVal,agent_info_id,'"+newPlanNo+"',comm_rate,oper_user,sysdate,MARKET_RATE,BUSINESS_RATE "
					+" from t_trad_agent_info_rela where insu_plan_no ='"+oldPlanNo+"'";

			pstmt = conn.prepareStatement(sql);
			
			logger.debug("执行SQL：" + sql);

			int row = pstmt.executeUpdate(sql);
			if(row>0){
				flag = true;
			}
		}finally{
			close(pstmt);
			close(conn);
		}
		return flag;
	}

	@Override
	public Page<Map<String, Object>> queryAgentInfoRelaWithUserPageByCriteria(Page<Map<String,Object>> page) throws Exception {
		Map<String,Object> paramMap = page.getMap();
		
		int total = (Integer) this.query("queryAgentInfoRelaCountByFilter", paramMap);
		page.setTotal(total);
		
		List<Map<String,Object>> resultList = this.queryList("queryAgentInfoRelaWithUserPageByFilter", paramMap);
		page.setData(resultList);

		return page;
	}

	@Override
	public Page<Map<String, Object>> queryAgentInfoRelaWithChannelPageByCriteria(
			Page<Map<String, Object>> page) throws Exception {
		Map<String,Object> paramMap = page.getMap();
		
		int total = (Integer) this.query("queryAgentInfoRelaCountByFilter", paramMap);
		page.setTotal(total);
		
		List<Map<String,Object>> resultList = this.queryList("queryAgentInfoRelaWithChannelPageByFilter", paramMap);
		page.setData(resultList);

		return page;
	}
	
	@Override
	public Page<Map<String, Object>> queryAgentInfoRelaWithCombinedPageByCriteria(
			Page<Map<String, Object>> page) throws Exception {
		Map<String,Object> paramMap = page.getMap();
		
		int total = (Integer) this.query("queryAgentInfoRelaCountByFilter", paramMap);
		page.setTotal(total);
		
		List<Map<String,Object>> resultList = this.queryList("queryAgentInfoRelaWithChannelPageByFilterOrCombined", paramMap);
		page.setData(resultList);

		return page;
	}

	@Override
	public List<TradAgentInfoRela> queryTradAgentInfoRelaByMap(TradAgentInfoRela tradAgentInfoRela) throws Exception {
		// TODO Auto-generated method stub
		List<TradAgentInfoRela> list=  this.queryList("queryTradAgentInfoRelaByMap", tradAgentInfoRela);
		return list;
	}

	@Override
	public  List<Map<String, Object>> impNlifeRela(Map<String, Object> map)
			throws Exception {
		return this.queryList("impNlifeRelaMap", map);
	}

	@Override
	public boolean addTradAgentInfoRelaBach(List<TradAgentInfoRela>  tradAgentInfoRelas) throws Exception {
		this.batchInsert("insertTradAgentInfoRela", tradAgentInfoRelas);
		return true;
	}

	@Override
	public Page<Map<String, Object>> queryZonAgentInfoRelaWithPageByCriteria(
			Page<Map<String, Object>> page) throws Exception {
		Map<String, Object> paramMap=page.getMap();
		int count=(Integer) this.query("queryAgentInfoRelaWithZoncount", paramMap);
		page.setTotal(count);
		List<Map<String,Object>> resultList = this.queryList("queryAgentInfoRelaWithZonPageByFilter", paramMap);
		page.setData(resultList);
		return page;
	}

	@Override
	public boolean delZonTradInfo(Map<String, Object> map) throws Exception {
		// TODO Auto-generated method stub
		return this.delete("delZonTradInfoRate", map);
	}

	@Override
	public void  addZonTradInfo(List<TradAgentInfoRela> list) throws Exception{
		this.batchInsert("addZonCommRate", list);
	}
    
	@SuppressWarnings("unchecked")
	@Override
    public List<Map<String,Object>> queryAgentInfoList(String deptNo,String planNo,String partnerNo) throws Exception{
		
		Map<String,Object> map=new HashMap<String,Object>();
		map.put("deptNo", deptNo);
		map.put("planNo", planNo);
		map.put("partnerNo", partnerNo);
		List<Map<String,Object>> resultList=this.queryList("queryAgentInfoRelaByCard", map);
		return resultList;
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<TradAgentInfoRela> selectCarPlanCodeInfoList(String partnerCode,String subChannelCode)
			throws Exception {
		Map<String,Object> map=new HashMap<String,Object>();
		map.put("channelCode", partnerCode);
		map.put("subChannelCode", subChannelCode);
		return this.queryList("selectCarPlanCode", map);
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<TradAgentInfoRela> selectAlreadyExistsCarInfoList(
			String partnerCode, String subChannelCode) throws Exception {
		Map<String,Object> map=new HashMap<String,Object>();
		map.put("channelCode", partnerCode);
		map.put("subChannelCode", subChannelCode);
		
		return this.queryList("selectAlreadyExistsCarInfo", map);
	}

	@Override
	public void updateAlreadyExistsCarInfo(List<TradAgentInfoRela> list)
			throws Exception {
		 try {
			this.batchUpdate("updateAlreadyExistsCarInfo", list);
		} catch (Exception e) {
			logger.error(e);
		}
	}

	@Override
	public void insertIntoAgentRealForCar(List<TradAgentInfoRela> list)
			throws Exception {
		if(null!=list && list.size()> 0){
			try {
				this.add("insertIntoAgentRealForCar", list);
			} catch (Exception e) {
				logger.error(e);
				throw new AppException("数据库应用忙，请稍后重试！");
			}
		}
	}

	@Override
	public void deleteAgentInfoRela(String id)throws Exception {
		this.delete("delTradAgentInfoRelaByAgentNo", id);
	}
	@Override
	public List<Map<String, Object>> impNocarRela(Map<String, Object> map)
			throws Exception {
		
		return this.queryList("impNocarRelaMap", map);
	}

	@Override
	public Page<Map<String, Object>> queryZonAgentInfoRelaWithPageByParam(
			Page<Map<String, Object>> page) throws Exception {
		Map<String, Object> paramMap=page.getMap();
		int count=(Integer) this.query("queryAgentInfoRelaWithZoncountByParam", paramMap);
		page.setTotal(count);
		List<Map<String,Object>> resultList = this.queryList("queryAgentInfoRelaWithZonPageByParam", paramMap);
		page.setData(resultList);
		return page;
	}

	@Override
	public void addZonTradInfo5(List<TradAgentInfoRela> list) throws Exception {
		this.batchInsert("addZonCommRate5", list);
	}

	@Override
	public boolean delZonTradInfo5(Map<String, Object> map) throws Exception {
		return this.delete("delZonTradInfoRate5", map);
	}

	@Override
	public List<TradAgentInfoRela> selectAgentRela(String id)throws Exception {
		return this.queryList("selectAgentInfoRela", id);
	}
}
